/**
 * 〈一句话功能简述〉<br>
 * 〈〉
 *
 * @author Administrator
 * @create 2019/3/13 0013
 */
package Service;


import Service.Utils.TransferDateUtil;
import com.mysql.jdbc.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 〈一句话功能简述〉<br> 
 * 〈〉
 *
 * @author Administrator
 * @create 2019/3/13 0013
 */
public class YibaiChandaoTaskServiceImpl {

    public YibaiChandaoTaskServiceImpl() {
    }

    public List<Map<String, Object>> getAll(String theMonth ,int theWeek ) {
        try {
            String driver = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://192.168.71.165:3306/zentao?zeroDateTimeBehavior=convertToNull&useOldAliasMetadataBehavior=true";
            String user = "chenjiao";
            String pass = "Chenj@2018";
            String querySql = "SELECT "+
                        "ztt.id AS taskId, "+
                        "zts.id AS storyId, "+
                        "ztp.`name` AS projectName ,"+
                        "zts.title AS storyName, "+
                        "CONCAT(zts.id,':',zts.title) AS storyNumName, "+
                        "ztt.`name` AS taskName, "+
                        "oau.user_name AS storyDishedBy, "+
                        "zts.dishedDate AS storyDishedDate, "+
                        "ztuname.realname AS storyReviewBy, "+
                        "zts.reviewedDate AS storyReviewDate, "+
                        "ztt.deadline AS finishDete, "+
                        "IFNULL(TO_DAYS(ztt.deadline) - TO_DAYS(ztt.estStarted),0) AS duration,  "+
                        "ztt.consumed AS consumedTime, "+
                        "ztt.estimate AS estimatedTime, "+
                        "ztt.estStarted AS realStartTime, "+
                        "ztt.finishedDate AS realFinishedDate, "+
                        "ztua.realname AS assignedBy, "+
                        "ztuf.realname AS finishedBy, "+
                        //  ztt.`status` AS taskStatus,
                        "CASE WHEN ztt.`status`='wait' THEN '未开始' WHEN ztt.`status`='doing' THEN '进行中' WHEN ztt.`status`='done' THEN '完成' WHEN ztt.`status`='pause' THEN '暂停' WHEN ztt.`status`='cancel' THEN '取消' "+
                        "WHEN ztt.`status`='closed' THEN '关闭' ELSE NULL END AS  taskStatus, "+
                        "FLOOR(ztt.consumed / (ztt.consumed + ztt.`left`) * 100) AS percentage, "+
                        "DATE_FORMAT(SUBDATE(?, INTERVAL 7 DAY),'%Y-%m') AS sweepMonth, "+
                        "((DAY (SUBDATE(?, INTERVAL 7 DAY)) + WEEKDAY(SUBDATE(?, INTERVAL 7 DAY) - INTERVAL DAY (SUBDATE(?, INTERVAL 7 DAY)) DAY)) DIV 7)+1 AS weeks "+
                    "FROM zt_task ztt "+
                        "LEFT JOIN zt_story zts ON ztt.story = zts.id "+
                        "LEFT JOIN zt_project ztp ON ztt.project = ztp.id "+
                        "LEFT JOIN oa_user oau ON oau.user_number = zts.dishedBy "+
                        "LEFT JOIN zt_user ztuname ON ztuname.account = zts.reviewedBy "+
                        "LEFT JOIN zt_user ztua "+
                        "ON (ztua.account = ztt.assignedTo AND ztt.`status` != 'done') OR (ztua.account = ztt.finishedBy) "+
                        "LEFT JOIN zentao.zt_user ztuf ON ztuf.account = ztt.finishedBy "+
                    "WHERE ztt.deleted = '0' "+
                       " AND NOT EXISTS ( SELECT 1 FROM zentao.zt_task bb WHERE bb.parent = ztt.id ) "+
                        "AND ( ( ztt.`status` NOT IN ('cancel', 'done', 'closed') AND DATE_FORMAT( ztt.assignedDate, '%Y-%m-%d' ) <= ? ) "+
                        "OR ( DATE_FORMAT( ztt.finishedDate, '%Y-%m-%d' ) BETWEEN  ?  AND ? ) ) "+
                        "GROUP BY ztt.id "+
                        "ORDER BY IF(isnull(zts.id), 0, 1) DESC, zts.id ";

            Map<String,Object> params = new HashMap<String,Object>();
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, user, pass);
            PreparedStatement stmt =  conn.prepareStatement(querySql);

            // 获取指定周数据
            String [] arr  = theMonth.split("-");
            int year = Integer.valueOf(arr[0]);
            int month = Integer.valueOf(arr[1]);
//            System.out.println(year);
//            System.out.println(month);
            Map<String,String> dateMap = TransferDateUtil.getMonAndSunDay(year, month, theWeek);
            String firstDay = dateMap.get("firstDay");
            String lastDay = dateMap.get("lastDay");
            if(!StringUtils.isNullOrEmpty(lastDay) && !StringUtils.isNullOrEmpty(firstDay)){
                stmt.setString(1, lastDay);
                stmt.setString(2, lastDay);
                stmt.setString(3, lastDay);
                stmt.setString(4, lastDay);
                stmt.setString(5, lastDay);
                System.out.println("123457lastDay ="+lastDay);
                stmt.setString(6, firstDay);
                System.out.println("6firstDay ="+firstDay);
                stmt.setString(7, lastDay);
                System.out.println("获取数据为:"+firstDay+"-"+lastDay+"期间数据");

            }else{
                // 否则获取上周数据
               Map<String , String> timeMap =   TransferDateUtil.getLastTimeInterval();
               String lastBeginDate = timeMap.get("lastBeginDate");
               String lastEndDate = timeMap.get("lastEndDate");
               stmt.setString(1, lastEndDate);
               stmt.setString(2, lastEndDate);
               stmt.setString(3, lastEndDate);
               stmt.setString(4, lastEndDate);
               stmt.setString(5, lastEndDate);
               stmt.setString(6, lastBeginDate);
               stmt.setString(7, lastEndDate);
               System.out.println("获取数据为:上周"+lastBeginDate+"-"+lastEndDate+"数据");
            }

            ResultSet rs = stmt.executeQuery();
            int columnCount = rs.getMetaData().getColumnCount();
            List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
            while (rs.next()) {
                Map<String,Object> rowData = new HashMap<String,Object>();
                for (int i = 1; i <= columnCount; i++) {
                    Object temp1 = rs.getMetaData().getColumnName(i);
                    Object temp2 = rs.getObject(i);
                    rowData.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
//                    System.out.print(temp1+":"+temp2);
                }
                list.add(rowData);
            }
            if (rs != null) {//11.关闭记录集
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {//12.关闭声明的对象
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {//13.关闭连接 （记住一定要先关闭前面的11.12.然后在关闭连接，就像关门一样，先关里面的，最后关最外面的）
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return list;
        } catch (Exception e) {
            System.out.println("捕获异常");
            e.printStackTrace();
            return  null;
        }
    }
}
